﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Business.Struct;
using WF_Business;
using WF_DataAccess;
using System.Data.OracleClient;

namespace Business.Common
{
    /// <!--
    /// 功能描述  : 文件上传操作类
    /// 创建人  : LinJian
    /// 创建时间: 2007-07-09
    /// -->
    public class UploadFileClass
    {
        private string _newId;

        /// <summary>
        /// 新生成的id
        /// </summary>
        public string NewId
        {
            get
            {
                return _newId;
            }
            set
            {
                _newId = value;
            }
        }

        /// <summary>
        /// 验证上传文件后缀是否有效
        /// </summary>
        /// <param name="strFileExtension">文件扩展名</param>
        /// <returns></returns>
        public bool HaveValideFileExtension(string strFileExtension)
        {
            bool bOk = false;

            for (int i = 0; i < SystemConfig.UploadFileExtension.Length; i++)
            {
                if (strFileExtension.CompareTo(SystemConfig.UploadFileExtension[i]) == 0)
                {
                    bOk = true;
                    break;
                }
            }

            return bOk;
        }

        /// <summary>
        /// 获取存储文件信息
        /// </summary>
        /// <param name="fileUpload">文件上传信息</param>
        /// <returns>返回执行结果</returns>
        public object GetStoreFileType(FileUploadStruct fileUpload)
        {
            string strSql = string.Format(@"select {0} from {1} where {2}='{3}'",
                fileUpload.FileTypeFieldName,fileUpload.TableName, fileUpload.KeyId,fileUpload.KeyIdValue);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 设置用途
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strKeyId">id值</param>
        /// <param name="strUseType">用途</param>
        /// <returns></returns>
        public int SetUseType(string strTableName,string strKeyId,string strUseType)
        {
            string strSql = string.Format(@"update {0} set USE_TYPE={1} where ID='{2}'",
                strTableName, strUseType, strKeyId);
            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 删除存储文件信息
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <param name="strKeyId">键值名</param>
        /// <param name="strKeyIdValue">键值</param>
        /// <returns></returns>
        public int DeleteStoreFile(string strTableName,string strKeyId,string strKeyIdValue)
        {
            string strSql = string.Format(@"delete from {0} where {1}='{2}'",
                strTableName, strKeyId,strKeyIdValue);

            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取上传的资源信息
        /// </summary>
        /// <param name="strResourceName">查找的资源名</param>
        /// <param name="strUseType">用途类型</param>
        /// <returns></returns>
        public DataTable GetResourceInfo(string strResourceName,string strUseType)
        {
            string strSql = @"select rownum 序号,id,RESOURCE_NAME,RESOURCE_TYPE,USE_TYPE,
                wynewpackage.GetItemTypeName(USE_TYPE,t.paramid) rstype,Table_name from SYS_RESOURCE t where 1=1";

            if (!string.IsNullOrEmpty(strResourceName))
            {
                strSql += string.Format(" and RESOURCE_NAME like '%{0}%'", strResourceName);
            }
            if (!string.IsNullOrEmpty(strUseType))
            {
                strSql += string.Format(" and USE_TYPE='{0}'",strUseType);
            }
            strSql += " order by orderdield";

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 获取上传的资源信息
        /// </summary>
        /// <param name="strResourceName">查找的资源名</param>
        /// <param name="strUseType">流程名称</param>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--添加流程ID条件 edit by zhongjian 20091026-->
        public DataTable GetResourceInfo(string strResourceName, string strUseType, string strFlowID)
        {
            DataTable dtTemp;
            string strSql = @"select  rownum,a.* from (select id,flowid,TABLE_NAME,RESOURCE_NAME,RESOURCE_TYPE,USE_TYPE,ORDERDIELD,
                wynewpackage.GetItemTypeName(USE_TYPE,t.paramid) rstype from SYS_RESOURCE t where 1=1";

            if (!string.IsNullOrEmpty(strResourceName))
            {
                strSql += string.Format(" and RESOURCE_NAME like '%{0}%'", strResourceName);
            }
            if (!string.IsNullOrEmpty(strUseType))
            {
                strSql += string.Format(" and USE_TYPE='{0}'", strUseType);
            }
            if (!string.IsNullOrEmpty(strFlowID))
            {
                strSql += string.Format(" and flowid='{0}'", strFlowID);
            }

            strSql += "order by ORDERDIELD) a";

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetStoreFile(string strTabName, string strKeyId)
        {
            string strSql = string.Format(@"select RESOURCE_NAME,RESOURCE_TYPE,RESOURCE_CONTENT from {0} where id='{0}'", strKeyId);
            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 获取表格下载排序最大值
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--update by zhongjian 20091018-->
        public static string GetMax(string strFlowID)
        {
            string strCount = "";
            if (!string.IsNullOrEmpty(strFlowID))
            {
                string strSql = string.Format(@"select max(ORDERDIELD) from sys_resource where flowid='{0}'", strFlowID);
                strCount = SysParams.OAConnection().GetValue(strSql);
            }
            return strCount;
        }

        /// <summary>
        /// 存储文件上传信息
        /// </summary>
        /// <param name="fileUpload">文件上传信息</param>
        /// <param name="dType">数据存储类型</param>
        /// <returns>返回执行结果</returns>
        public int StoreUploadFile(FileUploadStruct fileUpload, DataStoreType dType)
        {
            string strSql = string.Empty;
            IDataAccess ida = SysParams.OAConnection(false);

            if (dType == DataStoreType.Update)
            {
                strSql = string.Format(@"update {0} set {1}=:fileValue,
                {2}=:fileType where {3}=:keyid returning id into :newId", fileUpload.TableName,
                     fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.KeyId);

                IDataParameter[] idp = new OracleParameter[4];

                idp[0] = new OracleParameter("fileValue", OracleType.Blob);
                idp[0].Value = fileUpload.FileBytes;

                idp[1] = new OracleParameter("fileType", OracleType.VarChar, 255);
                idp[1].Value = fileUpload.FileTypeFieldValue;

                idp[2] = new OracleParameter("keyid", OracleType.VarChar, 255);
                idp[2].Value = fileUpload.KeyIdValue;

                idp[3] = new OracleParameter("newId", OracleType.VarChar, 255);
                idp[3].Direction = ParameterDirection.Output;

                ida.RunSql(strSql, ref idp);

                _newId = idp[3].Value.ToString();
            }
            else if (dType == DataStoreType.Insert)
            {
                strSql = string.Format(@"insert into {0}({1},{2},{3},{4},{5}) values(:fileValue,
                :fileType,:keyField,:resourceField,:paramvalue) returning id into :newId", fileUpload.TableName,
                fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.StoreKeyFieldName,
                fileUpload.StoreResourceFieldName, fileUpload.StoreParamFieldName);

                IDataParameter[] idp = new OracleParameter[6];

                idp[0] = new OracleParameter("fileValue", OracleType.Blob);
                idp[0].Value = fileUpload.FileBytes;

                idp[1] = new OracleParameter("fileType", OracleType.VarChar, 255);
                idp[1].Value = fileUpload.FileTypeFieldValue;

                idp[2] = new OracleParameter("keyField", OracleType.VarChar, 255);
                idp[2].Value = fileUpload.StoreKeyValue;

                idp[3] = new OracleParameter("resourceField", OracleType.VarChar, 255);
                idp[3].Value = fileUpload.StoreResourceNameValue;

                idp[4] = new OracleParameter("paramvalue", OracleType.VarChar, 255);
                idp[4].Value = fileUpload.StoreParamValue;

                idp[5] = new OracleParameter("newId", OracleType.VarChar, 255);
                idp[5].Direction = ParameterDirection.Output;

                ida.RunSql(strSql, ref idp);

                _newId = idp[5].Value.ToString();
            }
            return 1;
        }

        /// <summary>
        /// 获取接口文件上传的资源信息
        /// </summary>
        /// <param name="strFileName">查找的文件名</param>
        /// <param name="strNewsId">地信中心外网申报信息id</param>
        /// <returns></returns>
        public DataTable GetAttachmentResourceInfo(string strFileName, string strNewsId)
        {
            string strSql = @"select id,FILEDATA,FILETYPE, 地信申报信息ID,FILENAME from XT_SERIAL_ATTACHMENT t where flag=0";

            if (!string.IsNullOrEmpty(strFileName))
            {
                strSql += string.Format(" and FILENAME like '%{0}%'", strFileName);
            }
            if (!string.IsNullOrEmpty(strNewsId))
            {
                strSql += string.Format(" and 地信申报信息ID='{0}'", strNewsId);
            }

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 获取接口文件上传的资源信息
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public DataTable GetAttachmentResourceInfo(string strWhere)
        {
            string strSql = @"select t.*,(select a.modulename from xt_serial_module a where a.id = t.module_id) fjlb from XT_SERIAL_ATTACHMENT t where 1=1 ";

            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql += string.Format(" and {0}", strWhere);
            }

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 设置附件为模板
        /// </summary>
        /// <param name="strId">附件id</param>
        /// <param name="strUserId">用户id</param>
        public int SetAtachmentMoudle(string strId, string strUserId)
        {
            string strSql =string.Format(@"update XT_SERIAL_ATTACHMENT set IS_TEMPLET='1',地信申报信息ID=null,USERID='{0}' where id='{1}'",strUserId,strId);

            return SysParams.OAConnection().RunSql(strSql);
        }


        /// <summary>
        /// 获取接口文件存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetAttachmentStoreFile(string strTabName, string strKeyId)
        {
            string strSql = string.Format(@"select 地信申报信息ID,FILEDATA,FILETYPE,FILENAME from {0} where id='{1}' and flag=0",strTabName,strKeyId);
            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 获取模板文件存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetModuleStoreFile(string strTabName, string strKeyId)
        {
            string strSql = string.Format(@"select id,FILEDATA,FILETYPE,FILENAME from {0} where id='{1}'",
                strTabName,strKeyId);
            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        

        /// <summary>
        /// 获取在线申报文件上传的资源信息
        /// </summary>
        /// <param name="strFileName">查找的文件名</param>
        /// <param name="strNewsId">地信中心外网申报信息id</param>
        /// <returns></returns>
        public DataTable GetCailiaoResourceInfo(string strFileName, string strNewsId)
        {
            string strSql = @"select id,FILEDATA,FILETYPE, 地信申报信息ID,FILENAME from XT_SERIAL_ATTACHMENT t where flag=1";

            if (!string.IsNullOrEmpty(strFileName))
            {
                strSql += string.Format(" and FILENAME like '%{0}%'", strFileName);
            }
            if (!string.IsNullOrEmpty(strNewsId))
            {
                strSql += string.Format(" and 地信申报信息ID='{0}'", strNewsId);
            }

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 获取在线申报文件存储文件信息
        /// </summary>
        /// <param name="strTabName"></param>
        /// <param name="strKeyId"></param>
        /// <returns></returns>
        public DataTable GetCailiaoStoreFile(string strTabName, string strKeyId)
        {
            string strSql = string.Format(@"select 地信申报信息ID,FILEDATA,FILETYPE,FILENAME from {0} where id='{1}' and flag=1",strTabName,strKeyId);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 获取在线申报上传的资源信息
        /// </summary>
        /// <param name="strType">文件类型</param>
        /// <param name="strNewsId">地信中心外网申报信息id</param>
        /// <returns></returns>
        /// 添加排序功能 edit by zhongjian 20091018
        public DataTable GetCailiaoInfo(string strType, string strNewsId)
        {
            string strSql = string.Format(@"select rownum 序号,t.* from (select a.id as moduleid,a.modulename,a.type,
                a.filedata as mudulefiledata,a.filename||'.'||a.filetype as mudulefilename,
                b.id as attachid,b.地信申报信息ID,b.filedata as attachfiledata,
                b.filename||'.'|| b.filetype as attachfilename, b.flag,b.filename attfilename
                from (select * from XT_SERIAL_MODULE where type = '{0}') a,
                (select * from XT_SERIAL_ATTACHMENT where 地信申报信息ID = '{1}') b
                 where a.Id=b.Module_Id(+) ORDER BY a.ORDERDIELD) t", strType, strNewsId);

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }        

        /// <summary>
        /// 获取在线申报上传的资源信息(地信中心接口专用)
        /// </summary>
        /// <param name="strType">文件类型</param>
        /// <param name="strNewsId">地信中心外网申报信息id</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20090925-->
        public DataTable GetAttinterfaceInfo(string strType, string strNewsId)
        {
            string strSql = string.Format(@"select rownum 序号,a.id as moduleid,a.modulename,a.type,
                a.filedata as mudulefiledata,a.filename||'.'||a.filetype as mudulefilename,
                b.id as attachid,b.地信申报信息ID,b.filedata as attachfiledata,
                b.filename||'.'|| b.filetype as attachfilename, b.flag
                from (select * from XT_SERIAL_MODULE where type = '{0}') a,
                (select * from xt_serial_attinterface where 地信申报信息ID = '{1}') b
                 where a.Id=b.Module_Id(+)", strType, strNewsId);// order by a.modulename asc

            DataTable dtOut;
            SysParams.OAConnection().RunSql(strSql, out dtOut);
            return dtOut;
        }

        /// <summary>
        /// 获取已上传的数量
        /// </summary>
        /// <param name="strIId">iid</param>
        /// <param name="strFlowname">flowname</param>
        /// <returns></returns>
        public string GetAlreadyUpdateCount(string strIId,string strFlowname)
        {
            string strSql = string.Format(@"select count(*) from xt_serial_attachment where 
                地信申报信息ID='{0}' and module_id in (select id from xt_serial_module
                where type='{1}')", strIId, strFlowname);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获取需要数量
        /// </summary>
        /// <param name="strFlowname">flowname</param>
        /// <returns></returns>
        public string GetUpdateCount(string strFlowname)
        {
            string strSql = string.Format(@"select count(*) from xt_serial_module where type='{0}'", strFlowname);
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 删除材料
        /// </summary>
        /// <param name="strId"></param>
        public static void DeleteModule(string strId)
        {
            string strSql = string.Format("delete from XT_SERIAL_MODULE where id='{0}'", strId);

            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 清空材料
        /// </summary>附件
        /// <param name="strId"></param>
        public static void EmptyModule(string strId)
        {
            string strSql = string.Format("Update XT_SERIAL_MODULE set FILEDATA='',FILETYPE='',FILENAME='' where id='{0}'", strId);

            SysParams.OAConnection().RunSql(strSql);
        }

       

        /// <summary>
        /// 更新记录排序
        /// </summary>
        /// <param name="strCurrentNo">当前序号</param>
        /// <param name="strFlowId">类型id</param>
        /// <param name="strResourceId">资源id</param>
        /// <param name="nSortFlag">排序标志（0：降序，1：升序）</param>
        public void UpdateSort(string strCurrentNo, string strFlowId, string strResourceId, int nSortFlag)
        {
            int NewNo = Convert.ToInt32(strCurrentNo) - 1;

            if (nSortFlag == 0)//降序
            {
                NewNo--;
            }
            else//升序
            {
                NewNo++;
            }

            //将新序号对应记录更新为当前序号
            string strSql = string.Format("update sys_resource set ORDERDIELD={0} where ORDERDIELD={1} and flowid ='{2}'", strCurrentNo, NewNo, strFlowId);
            SysParams.OAConnection().RunSql(strSql);

            //将当前序号更新为新序号
            strSql = string.Format("update sys_resource set ORDERDIELD={0} where id = '{1}'", NewNo, strResourceId);
            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 根据条件获取材料
        /// </summary>
        /// <param name="strId"></param>
        /// <returns></returns>
        public DataTable GetModuleId(string strId)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select * from XT_SERIAL_MODULE where id = '{0}'", strId);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 按条件获取材料排序最大值
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--add by zhongjian 20091018-->
        public static string GetModuleMax(string strFlowID)
        {
            string strCount = "";
            if (!string.IsNullOrEmpty(strFlowID))
            {
                string strSql = string.Format(@"select max(ORDERDIELD) from XT_SERIAL_MODULE where flowid='{0}'", strFlowID);
                strCount = SysParams.OAConnection().GetValue(strSql);
            }
            return strCount;
        }

        /// <summary>
        /// 根据模板类别获取材料
        /// </summary>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--editby zhongjian 20091021-->
        public DataTable GetModuleType(string strFlowID)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select * from XT_SERIAL_MODULE where flowid = '{0}' order by orderdield", strFlowID);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

       

        /// <summary>
        /// 增加材料
        /// </summary>
        /// <param name="strModuleName">模板名称</param>
        /// <param name="strType">流程名称</param>
        /// <param name="strFlowID">流程ID</param>
        /// <param name="strMax">排序字段</param>
        /// <returns></returns>
        /// <!--editby zhongjian 20091021-->
        public static void InsertModule(string strModuleName, string strType, string strFlowID, string strMax)
        {
            if (!string.IsNullOrEmpty(strModuleName))
            {
                string strSql = string.Format("Insert into XT_SERIAL_MODULE (modulename,type,flowid,orderdield) VALUES ('{0}','{1}','{2}','{3}')", strModuleName, strType, strFlowID, strMax);

                SysParams.OAConnection().RunSql(strSql);
            }
        }

        /// <summary>
        /// 修改材料
        /// </summary> 
        /// <param name="strId">模板ID</param>
        /// <param name="strModuleName">模板名称</param>
        /// <param name="strType">流程名称</param>
        /// <param name="strFlowID">流程ID</param>
        /// <returns></returns>
        /// <!--editby zhongjian 20091021-->
        public static void UpdateModule(string strId, string strModuleName, string strType, string strFlowID)
        {
            if (!string.IsNullOrEmpty(strModuleName))
            {
                string strSql = string.Format("Update XT_SERIAL_MODULE set modulename='{1}',type='{2}',flowid='{3}' where id='{0}'", strId, strModuleName, strType, strFlowID);

                SysParams.OAConnection().RunSql(strSql);
            }
        }

   }
}
